﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;
using Snake.Cache;
using Snake.Extensions;
using Snake.Mapping;
using Snake.Utils;

namespace Snake.Core
{
    class SQLExecutor
    {
        private DbContext _context;
        private static Dictionary<Type, SqlDbType> _dbTypeMap;

        #region Constructor

        static SQLExecutor()
        {
            _dbTypeMap = new Dictionary<Type, SqlDbType>
            {
                {typeof(byte), GetDBType(typeof(byte))},
                {typeof(bool), GetDBType(typeof(bool))},
                {typeof(char), GetDBType(typeof(char))},
                {typeof(int), GetDBType(typeof(int))},
                {typeof(short), GetDBType(typeof(short))},    
                {typeof(long), GetDBType(typeof(long))},
                {typeof(float), SqlDbType.Real},                            
                {typeof(double), SqlDbType.Float},
                {typeof(decimal), SqlDbType.Decimal},
                {typeof(DateTime), GetDBType(typeof(DateTime))},
                {typeof(byte?), GetDBType(typeof(byte?))},
                {typeof(bool?), GetDBType(typeof(bool?))},
                {typeof(char?), GetDBType(typeof(char?))},
                {typeof(int?), GetDBType(typeof(int?))},
                {typeof(short?), GetDBType(typeof(short?))},    
                {typeof(long?), GetDBType(typeof(long?))},
                {typeof(float?), SqlDbType.Real},                            
                {typeof(double?), SqlDbType.Float},
                {typeof(decimal?), SqlDbType.Decimal},
                {typeof(DateTime?), GetDBType(typeof(DateTime?))},
            };
        }
        public SQLExecutor(DbContext context)
        {
            if (context == null)
                throw new ArgumentNullException("context");

            _context = context;
        }

        #endregion

        public static void Init<T>()
        {
            var foo = ExecuteCache<IEnumerable<T>>.Exec;
        }

        public TCollection Execute<TCollection>()
        {
            return ExecuteCache<TCollection>.Exec(this);
        }

        public IEnumerable<T> InnerExecute<T>()
        {
            var type = typeof(T);
            var entityMap = _context.GetEntityMap(type);
            if (entityMap == null)
                throw new NullReferenceException("类型没有找到: " + type);

            var assemble = (Func<SqlDataReader, T>)entityMap.Assemble;

            var sql = _context.Writer.GetResult();

            using (var connection = _context.ConnectionString)
            using (var command = new SqlCommand(sql, connection as SqlConnection))
            {
                PrepareParams(command);
                connection.Open();
                var reader = command.ExecuteReader();
                using (reader)
                {
                    var result = new List<T>();
                    while (reader.Read())
                    {
                        result.Add(assemble(reader));
                    }

                    return result;
                }
            }
        }


        #region---------------ExecuteSQL-----------

        /*
         * ExecuteNonQuery: 执行不返回数据行的操作，并返回一个int类型的数据。
         * ExecuteReader: 执行查询，并返回一个 DataReader 对象。
         * ExecuteScalar: 执行查询，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用。
         */

        /// <summary>
        /// 获取 泛型List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>() where T : class, new()
        {
            return Try(GetEnumerableExecuteQuery<T>);

        }


        /// <summary>
        /// 执行数据库查询 返回 IEnumerable<T/>
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> GetEnumerableExecuteQuery<T>()
        {
            var sql = _context.Writer.GetResult();
            SqlCommand command;
            IList<T> listModels = new List<T>();
            var connection = _context.ConnectionString;
            using (command = new SqlCommand(sql, connection as SqlConnection))
            {

                PrepareParams(command);
                connection.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T model = DynamicBuilder<T>.CreateBuilder(reader).Build(reader);
                    listModels.Add(model);

                }
                connection.Close();

            }
            return listModels;
        }

        /// <summary>
        /// 通过command 执行Reader查询 注意 command必须包含参数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public IEnumerable<T> GetEnumerableExecuteQuery<T>(IDbCommand command)
        {
            IList<T> listModels = new List<T>();
            using (command)
            {
                command.Connection.Open();
                var reader = Try(command.ExecuteReader);
                while (reader.Read())
                {
                    T model = DynamicBuilder<T>.CreateBuilder(reader).Build(reader);
                    listModels.Add(model);
                }
                command.Connection.Close();
            }
            return listModels;
        }

        /// <summary>
        ///  ExecuteNonQuery: 执行不返回数据行的操作，并返回一个int类型的数据。
        /// </summary>
        public int ExecuteNonQuery()
        {
            var sql = _context.Writer.GetResult();
            SqlCommand command;
            var connection = _context.ConnectionString;
            using (command = new SqlCommand(sql, connection as SqlConnection))
            {
                PrepareParams(command);
                connection.Open();
                var result = Try(command.ExecuteNonQuery);
                connection.Close();
                return result;
            }

        }

        /// <summary>
        /// 执行command 查询
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(IDbCommand command)
        {
            using (command)
            {
                command.Connection.Open();
                var result = Try(command.ExecuteNonQuery);
                command.Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// ExecuteScalar: 执行查询，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public T ExecuteScalar<T>()
        {
            var sql = _context.Writer.GetResult();
            SqlCommand command;
            var connection = _context.ConnectionString;
            using (command = new SqlCommand(sql, connection as SqlConnection))
            {
                PrepareParams(command);
                connection.Open();
                var result = Try(command.ExecuteScalar);
                connection.Close();
                return (T)result;
            }

        }

        /// <summary>
        /// 执行查询 by command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(IDbCommand command)
        {
            using (command)
            {
                command.Connection.Open();
                var result = Try(command.ExecuteScalar);
                command.Connection.Close();
                return (T)result;
            }
        }

        /// <summary>
        ///  ExecuteScalar: 执行查询，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用。
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            var sql = _context.Writer.GetResult();
            SqlCommand command;
            var connection = _context.ConnectionString;
            using (command = new SqlCommand(sql, connection as SqlConnection))
            {
                PrepareParams(command);
                connection.Open();
                var result = Try(command.ExecuteScalar);
                connection.Close();
                return result;
            }

        }

        #endregion

        /// <summary>
        /// 执行 泛型委托 并捕获异常
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="func"></param>
        /// <returns></returns>
        private T Try<T>(Func<T> func)
        {
            try
            {
                return func();
            }
            catch (Exception ex)
            {
                //_transaction.Rollback();
                //_transaction = null;
                throw ex;
            }
        }



        /// <summary>
        /// 处理参数
        /// </summary>
        /// <param name="command"></param>
        private void PrepareParams(SqlCommand command)
        {
            foreach (var pair in _context.Values)
            {
                var value = ((IValueProvider)pair.Value).Value;
                var type = value.GetType();

                if (type == typeof(string))
                    command.Parameters.Add(pair.Key, SqlDbType.NVarChar, 4000).Value = value;
                else
                    command.Parameters.Add(pair.Key, _dbTypeMap[type]).Value = value;
            }
        }

        //http://windev.wordpress.com/2007/01/01/utility-function-to-convert-type-to-sqldbtype/
        private static SqlDbType GetDBType(Type theType)
        {
            SqlParameter param;
            System.ComponentModel.TypeConverter tc;
            param = new SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(param.DbType);
            if (tc.CanConvertFrom(theType))
            {
                param.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {
                // try to forcefully convert
                try
                {
                    param.DbType = (DbType)tc.ConvertFrom(theType.Name);
                }
                catch (Exception)
                {
                    // ignore the exception
                }
            }
            return param.SqlDbType;
        }

        private static class ExecuteCache<T>
        {
            public static Func<SQLExecutor, T> Exec;

            static ExecuteCache()
            {
                if (Exec == null)
                {
                    Type type = typeof(T).GetGenericArguments()[0];
                    var method = typeof(SQLExecutor).GetMethod("InnerExecute").MakeGenericMethod(type);
                    var obj = Expression.Parameter(typeof(SQLExecutor), "obj");

                    var body = Expression.Call(obj, method);

                    Exec = Expression.Lambda<Func<SQLExecutor, T>>(body, new[] { obj }).Compile();
                }
            }
        }
    }
}
